// ---------------------------------------------------------
// Dung Vu's Import class
// Copyright (C) 2011 Dung Vu. All rights reserved.
// ---------------------------------------------------------

# region Includes...

using System;
using System.Data;
using System.Text;
using System.Data.OleDb;
using System.IO;
using System.Collections;
using System.Collections.Specialized; 

# endregion // Includes...

namespace common
{
    public class import
	{
        //Based from http://www.codeproject.com/KB/database/FinalCSVReader.aspx
        public static bool ImportFromCSV(string filetable, DataTable toDataTbl)
        {
            string toFolderName = fileFuncs.FileNamePath(filetable);
            filetable = fileFuncs.ShortFileName(filetable);
            // You can get connected to driver either by using DSN or connection string
            // Create a connection string as below, if you want to use DSN less connection. 
            //The DBQ attribute sets the path of directory which contains CSV files
            string strConnString = "Driver={Microsoft Text Driver (*.txt; *.csv)};Dbq=" + toFolderName + ";Extensions=asc,csv,tab,txt;Persist Security Info=False";
            string sql_select;
            System.Data.Odbc.OdbcConnection conn;

            //Create connection to CSV file
            conn = new System.Data.Odbc.OdbcConnection(strConnString.Trim());

            //Open the connection 
            conn.Open();
            //Fetch records from CSV
            sql_select = "SELECT * FROM [" + filetable + "]";

            System.Data.Odbc.OdbcDataAdapter obj_oledb_da = new System.Data.Odbc.OdbcDataAdapter(sql_select, conn);
            //Fill dataset with the records from CSV file
            obj_oledb_da.Fill(toDataTbl);
            conn.Close();
            return true;
        }
        private void sample_Import_CVS()
        {
            string fileName = "d://test//hose.csv";
            DataTable tbl = new DataTable();
            import.ImportFromCSV(fileName, tbl);
        }

        //http://stackoverflow.com/questions/15828/reading-excel-files-from-c
        /// <summary>
        /// This method checks if the user entered sheetName exists in the Schema Table
        /// </summary>
        /// <param name="sheetName">Sheet name to be verified</param>
        /// <param name="dtSchema">schema table </param>
        private static bool CheckIfSheetNameExists(string sheetName, DataTable dtSchema)
        {
            foreach(DataRow dataRow in dtSchema.Rows)
            {
                    if( sheetName == dataRow["TABLE_NAME"].ToString() )
                    {
                            return true;
                    }       
            }
            return false;
        }

        // http://www.codeproject.com/KB/office/ExcelToDataset.aspx

        private static StringCollection GetExcelSheetName(DataTable schemaTable)
        {
            string tmp;
            StringCollection sheetName = new StringCollection();
            foreach (DataRow schemaRow in schemaTable.Rows)
            {
                tmp = schemaRow["TABLE_NAME"].ToString().Trim();
                tmp = StringLibs.RemoveStartWith(tmp,new char[]{'\''});
                tmp = StringLibs.RemoveEndWith(tmp, new char[] { '\'' });
                sheetName.Add(tmp.ToUpper());
            }
            return sheetName;
        }
        public static bool ImportFromExcel(string fileName, StringCollection sheetName, string fields, bool hasHeaders, DataTable toTbl)
        {
            if (sheetName != null)
            {
                for (int idx = 0; idx < sheetName.Count; idx++)
                {
                    sheetName[idx] = sheetName[idx].Trim().ToUpper();
                    if(sheetName[idx].LastIndexOf('$')<=0) sheetName[idx] += "$";
                }
            }

            string strConn = SysLibs.ExcelConnectionString(fileName, hasHeaders);
            using (OleDbConnection conn = new OleDbConnection(strConn))
            {
                conn.Open();
                DataTable schemaTable = conn.GetOleDbSchemaTable(
                OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
                StringCollection tblSheets = GetExcelSheetName(schemaTable);
                for (int idx = 0; idx < tblSheets.Count; idx++)
                {
                    string sheet = tblSheets[idx].Trim().ToUpper();
                    if (sheet.LastIndexOf('$') == 0) sheet += "$";
                    if (sheetName != null && !sheetName.Contains(sheet)) continue;
                    OleDbCommand cmd = new OleDbCommand("SELECT " + fields + " FROM [" + sheet + "]", conn);
                    cmd.CommandType = CommandType.Text;
                    new OleDbDataAdapter(cmd).Fill(toTbl);
                }
            }
            return true;
        }
        public static bool ImportFromExcel(string fileName, string sheetName, DataTable toTbl)
        {
            return ImportFromExcel(fileName, sheetName, "*", true, toTbl);
        }
        public static bool ImportFromExcel(string fileName, string sheetName, string fields, DataTable toTbl)
        {
            return ImportFromExcel(fileName, sheetName, fields, true, toTbl);
        }
        public static bool ImportFromExcel(string fileName, string sheetName, string fields, bool hasHeaders, DataTable toTbl)
        {
            StringCollection sheets =  new StringCollection();
            sheets.Add(sheetName);
            return ImportFromExcel(fileName, sheets, fields, hasHeaders, toTbl);
        }
        public static StringCollection GetExcelSheetName(string fileName)
        {
            string strConn = SysLibs.ExcelConnectionString(fileName, true);
            using (OleDbConnection conn = new OleDbConnection(strConn))
            {
                conn.Open();
                DataTable schemaTable = conn.GetOleDbSchemaTable(
                OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
                return GetExcelSheetName(schemaTable);
            }
        }
    }
}
